﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for NewsController
/// </summary>
public class NewsController:Interface
{
    private SqlConnection con;
	public NewsController()
	{
	    con = ConnectDB.Connect();
        ConnectDB.Open();
	}

    public int Insert(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_News";
            cmd.CommandType = CommandType.StoredProcedure;
            News n = (News)obj;
            cmd.Parameters.Add("@title", SqlDbType.NText).Value = n.Title;
            cmd.Parameters.Add("@category_news_id", SqlDbType.Int).Value = n.CategoryNews_id;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = n.Image;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = n.Description;
            cmd.Parameters.Add("@content", SqlDbType.NText).Value = n.Content;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = n.Url;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = n.Status;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = n.DateStart;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Update(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update_News";
            cmd.CommandType = CommandType.StoredProcedure;
            News n = (News)obj;
            cmd.Parameters.Add("@news_id", SqlDbType.NText).Value = n.News_id;
            cmd.Parameters.Add("@title", SqlDbType.NText).Value = n.Title;
            cmd.Parameters.Add("@category_news_id", SqlDbType.Int).Value = n.CategoryNews_id;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = n.Image;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = n.Description;
            cmd.Parameters.Add("@content", SqlDbType.NText).Value = n.Content;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = n.Url;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = n.Status;
            cmd.Parameters.Add("@dateUpdate", SqlDbType.DateTime).Value = n.DateUpdate;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Delete(int id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete_News";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@news_id", SqlDbType.Int).Value = id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public DataTable GetAll()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_News ORDER BY dateStart DESC";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
    public DataTable GetNewByCateId(int CateId)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_News where category_news_id=@cateId ORDER BY dateStart DESC";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("cateId",CateId);
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }

    public DataTable GetByID(int id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_News WHERE news_id=@id ORDER BY dateStart DESC";
            cmd.Parameters.AddWithValue("@id", id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;

            throw;
        }
    }
    public DataTable GetByProductID(int product_id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT n.* FROM News AS n INNER JOIN Product_News AS pn ON n.news_id=pn.news_id INNER JOIN Product AS p ON pn.product_id=p.product_id WHERE p.product_id=34 ORDER BY n.dateStart DESC";
            cmd.Parameters.AddWithValue("@product_id", product_id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {
            return null;

            throw;
        }
    }
    public List<News> GetListNewsAll()
    {
        List<News> list = new List<News>();
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_News ORDER BY dateStart DESC";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            foreach (DataRow item in dt.Rows)
            {
                News n = new News(item);
                list.Add(n);
            }
        }
        catch (Exception)
        {
            throw;
        }
        return list;
    }
}